prompt [住院]费用拆分业务过程 SP_ZY_FYJS00_FYCFJS
create or replace procedure SP_ZY_FYJS00_FYCFJS
(
 PBRID00  in  number    ,--[1][入参]:BRID00
 PZYID00  in  number    ,--[2][入参]:ZYID00
 PZYH000  in  varchar2  ,--[3][入参]:住院号
 PJZDH00  in  number    ,--[4][入参]:结账单号
 PCZY000  in  number    ,--[5][入参]:操作员编码
 PCZYXM0  in  varchar2  ,--[6][入参]:操作员姓名
 PCZYKS0  in  number    ,--[7][入参]:操作员科室
 PCZLX00  in  varchar2  ,--[8][入参]:操作类型 1:出院结算 2:重新结算
 PFYCFLX  in  varchar2  ,--[9][入参]:费用拆分类型 N:否 Y:婴儿拆分 M:医保项目拆分 8:医保跨年拆分
 PTYPE00  in  varchar2  ,--[10][入参]:类型 中途结算、医保病人年度结算
 PQTCS00  in  varchar2  ,--[11][入参]:其他参数(xml)
 PHJJE00  in  number    ,--[12][入参]:合计金额
 PCOMMIT  in  varchar2  ,--[13][入参]:是否提交事务 Y:是 N:否
 PZXZT00  out varchar2  ,--[14][出参]:执行状态 0:失败 1:成功
 PZFHJJE  out number    ,--[15][出参]:自费合计金额或上年度合计金额
 PYBHJJE  out number    ,--[16][出参]:医保合计金额或下年度合计金额
 PFKLSH0  out varchar2  ,--[17][出参]:分开结算流水号
 PNDJSXX  out varchar2  ,--[18][出参]年度结算返回信息=SF_ZY_SFQYYBKNDJS返回值
 POUTSTR  out varchar2  ,--[19][出参]备用出参xml格式输出
 PERCODE  out varchar2  ,--[20][出参]:错误编码
 PERRMSG  out varchar2   --[21][出参]:错误信息
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhangyc     2020.12.28  created  by ZYSF-20201229-004
-- zhangyc     2022.01.04  增加IC_YBBRLB.YBSDLB=2(不分开结算) 医保属地类别；0本地、1省内、2省外（未定义的都默认为本地0） by  	ZYSF-20220105-005
-- zhangyc     2022.01.05  增加drg相关信息判断 
LS_COUNT0     number(12);
LS_CZRQ00  char(8);
LS_CZSJ00  char(8);
LS_YBBRLB     BM_BRXXB0.YBBRLB%type;   --医保病人类别
LS_YBLB00     BM_BRXXB0.YBLB00%type;   --医保类别
LS_YBZXLB     IC_YBBRLB.YBZXLB%type;   --医保中心类别编码
LS_FBBH00     BM_BRXXB0.FBBH00%type;   --病人费别
LS_CXLSH0  	  PAY_YJJ_MXCX.CXLSH0%type;
LS_DDLSH0     PAY_YJJ_MXCX.DDLSH0%type;
LS_JKFH00  varchar2(10);   --是否启用 Y:启用 N:不启用
LS_YBNDCS  varchar2(100);  --年度结算参数
LS_NDCS00  varchar2(20);   --年度结算启用参数
LS_NDCS01  varchar2(100);  --年度结算启用分中心
LS_RYRQ00  ZY_BRXXB0.RYRQ00%type; --入院日期
LS_DBZBM0  ZY_BRXXB0.DBZBM0%type; --单病种编码
LS_RYLB00  ZY_BRXXB0.RYLB00%type; --入院类别
LS_SFJZFP  ZY_BRXXB0.SFJZFP%type; --是否精准扶贫 Y:是 1：是
LS_BZBMLB  ZY_BRXXB0.BZBMLB%type; --病种编码类别 0:单病种编码 1:精准扶贫编码 2:出院诊断
LS_YBMC00  IC_YBBRLB.YBMC00%type; --医保中心名称
LS_TSBZFL    BM_TSBZB0.TSBZFL%type;--病种分类 0:特殊病种 1:生育病种 2:单病种
LS_DQRQ00  varchar2(8);--当前日期
LS_SNDRQ0  varchar2(8);--上年度日期
LS_SNDFY0  number(12,2);--上年度费用
LS_BNDRQ0  varchar2(8);--本年度日期
LS_BNDFY0  number(12,2);--本年度费用
LS_BQBM00  varchar2(20);--病情编码
LS_ID0000_SND  BM_ZYCF00_TEMP00.ID0000%type; --上年度
LS_ID0000_BND  BM_ZYCF00_TEMP00.ID0000%type; --本年度
LS_CZBZ00  varchar2(2);--操作标志 0:正常结算 1:中途结算 2:年度结算 3:逃跑结算  01:跨年上年度 02:跨年本年度
LS_SFLB00  varchar2(1);--收费类别0:项目 1:YF_ZYCFMX 2:YF_YZYPSQ 3:药品  S:跨年上年度 B:跨年本年度
LS_YBSDLB  IC_YBBRLB.YBSDLB%type; --医保属地类别；0本地、1省内、2省外（未定义的都默认为本地0）

ZY_DRGLSH          ZY_BRXXB0.DRGLSH%type;--drglsh:DRG分组流水号
ZY_DRG_CODE        ZY_BRXXB0.DRG_CODE%type;--drg_code:DRG组编码
ZY_DRG_NAME        ZY_BRXXB0.DRG_NAME%type;--drg_name:DRG组名称
ZY_BATCHNUM        ZY_BRXXB0.BATCHNUM%type;--batchnum:批次号
ZY_DRGZFE          ZY_BRXXB0.DRGZFE%type;--drgzfe:DRG付费标准
ZY_DRG_STATUS      ZY_BRXXB0.DRG_STATUS%type;  --DRG同步状态:(0:撤销病案，1:病案上传，2:撤销明细，3:上传明细，4:分组查询，5:项目结算)
ZY_JSBLLX          ZY_BRXXB0.JSBLLX%type;--DRG结算病例类型(BM_YBSXZD.JKBM00=90 SXBM00=jsbllx)(1:按DRG结算 2:按项目结算 3:按床日结算 4:日间手术)
ZY_IN_DRG          ZY_BRXXB0.IN_DRG%type;--DRG入组标识编码(BM_YBSXZD.JKBM00=90 SXBM00=in_drg)     


begin
  PZXZT00:='0';--输出:执行状态 0:失败 1:成功
  LS_CZRQ00:=to_char(sysdate,'YYYYMMDD');
  LS_CZSJ00:=to_char(sysdate,'HH24:MI:SS');
  PZFHJJE:=0;--输出:自费合计金额
  PYBHJJE:=0;--输出:医保合计金额
  PERCODE:='';--输出:错误编码
  PERRMSG:='';--输出:错误信息
  PFKLSH0:='FYCF'||to_char(sysdate,'YYYYMMDDHH24MISS')||PZYID00;
  LS_CXLSH0:=PFKLSH0;
  LS_DDLSH0:=PFKLSH0;
  LS_DQRQ00:=to_char(sysdate,'YYYYMMDD');
  LS_SNDFY0:=0;--上年度费用
  LS_BNDFY0:=0;--本年度费用
  LS_SNDRQ0:=to_number(substrb(LS_DQRQ00,1,4))-1||'1231';--上年度日期
  LS_BNDRQ0:=LS_DQRQ00;--本年度日期
  LS_JKFH00:='N'; 
  PNDJSXX:='';--[出参]年度结算返回信息=SF_ZY_SFQYYBKNDJS返回值
  begin
  select A.YBBRLB,A.FBBH00,A.YBLB00,B.YBZXLB,C.RYRQ00,C.DBZBM0,C.RYLB00,C.SFJZFP,C.BZBMLB,nvl(B.YBSDLB,0) YBSDLB,
  C.DRGLSH,C.DRG_CODE,C.DRG_NAME,C.BATCHNUM,C.DRGZFE,C.DRG_STATUS,C.JSBLLX,C.IN_DRG 
  into LS_YBBRLB,LS_FBBH00,LS_YBLB00,LS_YBZXLB,LS_RYRQ00,LS_DBZBM0,LS_RYLB00,LS_SFJZFP,LS_BZBMLB,LS_YBSDLB,
  ZY_DRGLSH,ZY_DRG_CODE,ZY_DRG_NAME,ZY_BATCHNUM,ZY_DRGZFE,ZY_DRG_STATUS,ZY_JSBLLX,ZY_IN_DRG
  from BM_BRXXB0 A,IC_YBBRLB B,ZY_BRXXB0 C
   where A.FBBH00=B.FBBH00 and a.YBLB00=b.YBLB00 and A.BRID00=C.BRID00
   and A.BRID00 = PBRID00 and C.ZYID00=PZYID00;
  exception
     when others then
       LS_YBBRLB:='0';
       LS_YBLB00:='0';
       LS_YBZXLB:='0';
  end;
  if  PFYCFLX='8' and  nvl(PTYPE00,'出院结算') not in ('医保病人年度结算','中途结算','欠费出院登记') then --参考过程SF_ZY_SFQYYBKNDJS
  	 --步骤1：获取参数值
	 --ZY_YBBRCYJSSFQYKNJSMS:住院医保病人出院结算是否启用跨年结算模式,0:不启用,2:医保都启用,1:选择启用,加分号后面带启用的医保中心,多个中心用逗号隔开,如（1;@,H,2）默认0
  begin 
   select substrb(trim(VALUE0),1,100) into  LS_YBNDCS  from XT_XTCS00 where NAME00 in ('ZY_YBBRCYJSSFQYKNJSMS');
   exception
    when others then
     LS_YBNDCS:='0';
   end;
   LS_NDCS00:=trim(substrb(LS_YBNDCS,1,1));--0:不启用,2:医保都启用,1:选择启用
   LS_NDCS01:=trim(substrb(LS_YBNDCS,3,100));--多个中心
   if LS_NDCS00 in ('2') then --2:医保都启用 
  	 LS_JKFH00:='Y';  --Y:拆分 N:不拆分 
   elsif LS_NDCS00 in ('1') then --1:选择启用
  	 LS_JKFH00:='Y';  --Y:拆分 N:不拆分 
   else
 	  LS_JKFH00:='N';  --Y:拆分 N:不拆分 
   end if;
  --判断医保中心
   if LS_NDCS00 in ('1') then --1:选择启用
   	 if instrb(','||trim(LS_NDCS01)||',',','||trim(LS_YBZXLB)||',')>0 then 
   	 	 LS_JKFH00:='Y';  --Y:拆分 N:不拆分 
   	 else
   	 	 LS_JKFH00:='N';  --Y:拆分 N:不拆分 
   	 end if;
   end if;
   --判断入院日期和当前日期 是同一年度,不处理--begin--
   if substrb(LS_RYRQ00,1,4)=substrb(LS_DQRQ00,1,4) then 
 	  LS_JKFH00:='N'; --Y:拆分 N:不拆分 
   end if;
   --判断入院日期和当前日期 是同一年度,不处理--End--
   --2019.12.24 zhangyc 单病种 、生育类别（生育,生育实时刷卡住院） 不启用--begin--
   --2022.01.04 IC_YBBRLB.YBSDLB=2(不分开结算) 医保属地类别；0本地、1省内、2省外（未定义的都默认为本地0） 
   if (LS_DBZBM0 is not null) or (LS_RYLB00 in ('生育','生育实时刷卡住院'))  or  (LS_YBSDLB in (2))then 
 	   LS_JKFH00:='N';  --Y:拆分 N:不拆分 
   end if;
   --2019.12.24 zhangyc 单病种 、生育类别（生育,生育实时刷卡住院）不启用--End--
   --2020.01.09 
   --福州市医保，扶贫病种编码，要求拆分(肺科医院)--begin--
   if LS_BZBMLB='1'  and  LS_YBMC00 in ('福州市医保中心') then 
   	 LS_JKFH00:='Y'; --Y:拆分 N:不拆分 
   end if;
   --福州市医保，扶贫病种编码，要求拆分(肺科医院)--end--
   --病种分类 0:特殊病种 1:生育病种 2:单病种--begin--
   LS_BQBM00:=substrb(SF_SF_GETXMLNOTE('BQBM00',PQTCS00),1,20);
  if trim(LS_BQBM00) is not null then 
   begin
   select TSBZFL into LS_TSBZFL  from BM_TSBZB0 where trim(BH0000)=trim(LS_BQBM00) and YBZXLB=LS_YBZXLB;
    exception
   when others then
      LS_TSBZFL:='';
    end;
    --1:生育病种 2:单病种 不拆分--begin--
   if LS_TSBZFL in ('1','2') then 
   	 LS_JKFH00:='N'; --Y:拆分 N:不拆分 
   end if;
   --1:生育病种 2:单病种 不拆分--end--  
  end if;
  --病种分类 0:特殊病种 1:生育病种 2:单病种--end--
  
  --2023.01.05 jsbllx：1:按DRG结算 2:按项目结算 3:按床日结算 4:日间手术
  if ZY_JSBLLX is not null and ZY_JSBLLX not in ('2') then 
    LS_JKFH00:='N'; --Y:拆分 N:不拆分 
  end if;


  --步骤3：判断上年度是否有没有费用
  if LS_JKFH00='Y' then
  	
  	--上年度费用
  	 select  SQ_ZY_TEMP00_ID0000.nextval  into  LS_ID0000_SND  from  dual;
  	 LS_CZBZ00:='01';--操作标志 0:正常结算 1:中途结算 2:年度结算 3:逃跑结算  01:跨年上年度 02:跨年本年度
  	 LS_SFLB00:='S';--收费类别0:项目 1:YF_ZYCFMX 2:YF_YZYPSQ 3:药品  S:跨年上年度 B:跨年本年度
  	 insert into ZY_JSBCMX(ZYID00,ID0000,MXID00,SFLB00,XMBH00,JZDH00,DJH000,XMDJ00,
                           XMSL00,HJJE00,CZRQ00,CZSJ00,CZY000,CZBZ00,FZDDH0,BZ0000)
  	 select  PZYID00,LS_ID0000_SND,0,LS_SFLB00,0,PJZDH00,A.DJH000,a.HJJE00,
  	  1,a.HJJE00,LS_CZRQ00,LS_CZSJ00,PCZY000,LS_CZBZ00,substrb(LS_DDLSH0,1,30),'FYCF_SND'
      from ZY_BRFY00 a where  1=1
      and a.ZYID00=PZYID00
      and a.BRID00=PBRID00
  	  and CZRQ00<=LS_SNDRQ0
  	  and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
                              where  k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
                                     connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
      ;
     --冲销的单据号
     insert into ZY_JSBCMX(ZYID00,ID0000,MXID00,SFLB00,XMBH00,JZDH00,DJH000,XMDJ00,
                           XMSL00,HJJE00,CZRQ00,CZSJ00,CZY000,CZBZ00,FZDDH0,BZ0000)
  	 select PZYID00,LS_ID0000_SND,0,LS_SFLB00,0,PJZDH00,A.DJH000,a.HJJE00,
  	  1,a.HJJE00,LS_CZRQ00,LS_CZSJ00,PCZY000,LS_CZBZ00,substrb(LS_DDLSH0,1,30),'FYCF_SND'
      from ZY_BRFY00 a where  1=1
      and a.ZYID00=PZYID00
      and a.BRID00=PBRID00
  	  and a.CXDJH0<>0
      and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
                              where  k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
                                     connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
  	  and a.CXDJH0 in     (select bb.DJH000 from  ZY_JSBCMX bb where bb.ZYID00=PZYID00 and bb.ID0000=LS_ID0000_SND  and bb.CZBZ00='01' )
  	  and a.DJH000 not in (select bb.DJH000 from  ZY_JSBCMX bb where bb.ZYID00=PZYID00 and bb.ID0000=LS_ID0000_SND  and bb.CZBZ00='01') 
      ;    
    begin 
     select sum(HJJE00) into LS_SNDFY0 from  ZY_JSBCMX 
      where 1=1
        and ZYID00=PZYID00
        and ID0000=LS_ID0000_SND
        and CZBZ00='01'
        ;
    exception
    when others then
     LS_SNDFY0:=0;
    end;
   
  	--本年度费用
  	select  SQ_ZY_TEMP00_ID0000.nextval  into  LS_ID0000_BND  from  dual;
  	LS_CZBZ00:='02';--操作标志 0:正常结算 1:中途结算 2:年度结算 3:逃跑结算  01:跨年上年度 02:跨年本年度
  	LS_SFLB00:='B';--收费类别0:项目 1:YF_ZYCFMX 2:YF_YZYPSQ 3:药品  S:跨年上年度 B:跨年本年度
  	insert into ZY_JSBCMX(ZYID00,ID0000,MXID00,SFLB00,XMBH00,JZDH00,DJH000,XMDJ00,
                           XMSL00,HJJE00,CZRQ00,CZSJ00,CZY000,CZBZ00,FZDDH0,BZ0000)
  	select  PZYID00,LS_ID0000_BND,0,LS_SFLB00,0,PJZDH00,A.DJH000,a.HJJE00,
  	  1,a.HJJE00,LS_CZRQ00,LS_CZSJ00,PCZY000,LS_CZBZ00,substrb(LS_DDLSH0,1,30),'FYCF_BND'
      from ZY_BRFY00 a where  1=1
      and a.ZYID00=PZYID00
      and a.BRID00=PBRID00
      and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
                              where  k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
                                     connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
  	  and a.DJH000 not in (select bb.DJH000 from  ZY_JSBCMX bb where bb.ZYID00=PZYID00 and bb.ID0000=LS_ID0000_SND and bb.CZBZ00='01')  ;
  	
  	begin 
     select sum(HJJE00) into LS_BNDFY0 from  ZY_JSBCMX 
      where 1=1
        and ZYID00=PZYID00
        and ID0000=LS_ID0000_BND
        and CZBZ00='02' 
        ;
    exception
    when others then
     LS_BNDFY0:=0;
    end;
  	--如果上年度无费用直接返回N
 	 if nvl(LS_SNDFY0,0)<=0 then 
 		LS_JKFH00:='N';
 	 end if; 	
 	 --如果本年度无费用直接返回N
 	 if nvl(LS_BNDFY0,0)<=0 then 
 		LS_JKFH00:='N';
 	 end if; 
 	 POUTSTR:='<SNDID0>'||LS_ID0000_SND||'</SNDID0>'||'<BNDID0>'||LS_ID0000_BND||'</BNDID0>';--[19][出参]备用出参xml格式输出	
   PZFHJJE:=nvl(LS_SNDFY0,0);--[15][出参]:自费合计金额或上年度合计金额
   PYBHJJE:=nvl(LS_BNDFY0,0);--[16][出参]:医保合计金额或下年度合计金额	 
   --PNDJSXX  out varchar2  ,--[18][出参]年度结算返回信息=SF_ZY_SFQYYBKNDJS返回值
   --第一位：是否启用Y:启用 N:不启用  
   --第二位: 上年度截止日期,费用
   --第三位: 本年度截止日期,费用
   PNDJSXX:=LS_JKFH00||'|';
   PNDJSXX:=PNDJSXX||LS_RYRQ00||','||LS_SNDRQ0||','||nvl(LS_SNDFY0,0);
   PNDJSXX:=PNDJSXX||'|';
   PNDJSXX:=PNDJSXX||substrb(LS_DQRQ00,1,4)||'0101'||','||LS_BNDRQ0||','||nvl(LS_BNDFY0,0);
  end if;
  --1:医保跨年拆分--end
  	
  elsif  PFYCFLX='Y' then
  	--1:出院结算 Y:婴儿拆分---begin---
  	--把数据插入到中间表-->婴儿费用
     insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
      select LS_CXLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','N'
      from ZY_BRFY00 a where  1=1
      and a.ZYID00=PZYID00
      and a.BRID00=PBRID00
  	  and a.YEXM00 is not null
  	  and a.yexm00 not in ('全部')
  	  and nvl(trim(a.YBFSDH),'0')='0'
  	  and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
                              where  k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
                                     connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
      ;
     
     --冲销的单据号
     insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
     select LS_CXLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','N'
      from ZY_BRFY00 a where  1=1
      and a.ZYID00=PZYID00
      and a.BRID00=PBRID00
  	  and a.CXDJH0<>0
  	  and a.CXDJH0 in (select bb.DJH000 from  PAY_YJJ_MXCX bb where bb.BRID00=PBRID00 and bb.CXLSH0=LS_CXLSH0 and bb.DDLSH0=LS_DDLSH0 and bb.STATE='N' )
  	  and a.DJH000 not in (select bb.DJH000 from  PAY_YJJ_MXCX bb where bb.BRID00=PBRID00 and bb.CXLSH0=LS_CXLSH0 and bb.DDLSH0=LS_DDLSH0 and bb.STATE='N') 
      ;
      
   --查询数据婴儿自费金额
   begin
  	 select sum(AMOUNT) into PZFHJJE from  PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=LS_CXLSH0 and DDLSH0=LS_DDLSH0 and STATE='N';
  	exception
     when others then
       PZFHJJE:=0;
   end;

   if nvl(PZFHJJE,0)<=0 then
   	PZFHJJE:=0;
   	PFKLSH0:='';
    return;
   end if;

  	 --把数据插入到中间表-->非婴儿费用
     insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
      select LS_DDLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','Y'
      from ZY_BRFY00 a where  1=1
      and a.ZYID00=PZYID00 and a.BRID00=PBRID00
  	  and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
                              where  k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
                                     connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)

  	  and
  	  (
  	  ( nvl(PZFHJJE,0)>0 and not exists (select 1 from  PAY_YJJ_MXCX aa where aa.DJH000=a.DJH000 and aa.BRID00=PBRID00
  	                                     and aa.CXLSH0=LS_CXLSH0 and aa.DDLSH0=LS_DDLSH0 and aa.STATE='N'))
  	  or (nvl(PZFHJJE,0)<=0 and 1=1)
  	  );


   --查询数据医保金额
   begin
  	 select sum(AMOUNT) into PYBHJJE from  PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=LS_CXLSH0 and DDLSH0=LS_DDLSH0 and STATE='Y';
  	exception
     when others then
       PYBHJJE:=0;
   end;

  --1:出院结算 Y:婴儿拆分---end---

   --1:出院结算 M:医保项目拆分---begin---
  elsif  PFYCFLX='M' then

  	 --把数据插入到中间表-->非医保项目--费用
     insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
  	select LS_DDLSH0,LS_DDLSH0,D.DJH000,sum(A.HJJE00)HJJE00,D.BRID00,0,'0','N'
          from   ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E
          where  1=1
                 and nvl(trim(d.YBFSDH),'0')='0'
                 and (d.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
                              where  k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
                                     connect by prior k.CXJZDH=k.JZDH00) or d.JZDH00+0=0)
                 and a.XMBH00 = C.SFXMID
                 and C.ZYFPID = B.FPXMID
                 and D.DJH000=a.DJH000
                 and a.KDYS00=e.YGBH00
                 and B.SFYLXM='Y'
                 and a.XMBH00<9999999900
                 and d.BRID00=PBRID00
                 and d.ZYID00=PZYID00
                 and a.JMBZ00<>'2'
                 and exists (
                 select 1  from  BM_YBFPXM AA,VW_BM_YBSFDY CC,BM_YBSFXM DD
                 where  AA.BH0000 = CC.YBZYFP
                           and  AA.YBZXLB = CC.YBZXLB
                           and  CC.YBBRLB = LS_YBBRLB
                           and  CC.SFXMID = A.XMBH00
                           and  CC.YBZXLB = DD.YBZXLB(+)
                           and  CC.YBXMBH = DD.XMBH00(+)
                           and  CC.SFYP00 = 'N'--是否药品,'0'费用,'1'零散处方,'2'医嘱处方
                           and  AA.YBZXLB = LS_YBZXLB
                           and  AA.SYBZ00 <> '1'
                           and  nvl(CC.SFYBXM,'N')='N'
                 )
                group by D.DJH000,D.BRID00;

        --把数据插入到中间表-->非医保项目--药品
        insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
        select LS_DDLSH0,LS_DDLSH0,D.DJH000,sum(round(a.LSDJ00*a.YPZSL0*100)/100)HJJE00,D.BRID00,0,'1','N'
          from   YF_ZYCFMX  A,YF_ZYCF00 c,ZY_BRFY00 d,BM_YD0000 e
          where 1=1
               and nvl(trim(D.YBFSDH),'0')='0'
               and (d.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
                               where  k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
                                      connect by prior k.CXJZDH=k.JZDH00) or d.JZDH00+0=0)
               and a.CFLSH0=c.CFLSH0
               and c.DJH000=d.DJH000
               and a.YPNM00=e.YPNM00
               and d.BRID00=PBRID00
               and d.ZYID00=PZYID00
               and a.SFZBY0='N'--是否自备药
               and exists (
                 select 1  from  BM_YBFPXM AA,VW_BM_YBSFDY CC,BM_YBSFXM DD
                 where  AA.BH0000 = CC.YBZYFP
                           and  AA.YBZXLB = CC.YBZXLB
                           and  CC.YBBRLB = LS_YBBRLB
                           and  CC.SFXMID = a.YPNM00
                           and  CC.YBZXLB = DD.YBZXLB(+)
                           and  CC.YBXMBH = DD.XMBH00(+)
                           and  CC.SFYP00 = 'Y'--是否药品,'0'费用,'1'零散处方,'2'医嘱处方
                           and  AA.YBZXLB = LS_YBZXLB
                           and  AA.SYBZ00 <> '1'
                           and  nvl(CC.SFYBXM,'N')='N'
                 )
                group by D.DJH000,D.BRID00;

         --把数据插入到中间表-->非医保项目--药品
        insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
        select LS_DDLSH0,LS_DDLSH0,b.DJH000,sum(round(a.LSDJ00*a.YPZSL0*100)/100)HJJE00,b.BRID00,0,'2','N'
          from   YF_YZYPSQ A,ZY_BRFY00 b,BM_YD0000 d,BM_YGBM00 E
          where 1=1
               and  nvl(trim(b.YBFSDH),'0')='0'
               and (b.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
                              where  k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
                                     connect by prior k.CXJZDH=k.JZDH00) or b.JZDH00+0=0)
                 and a.DJH000=b.DJH000
                 and a.YPNM00=d.YPNM00
                 and a.YSGZH0=e.YGBH00
                 and b.BRID00=PBRID00
                 and b.ZYID00=PZYID00
                 and exists (
                 select 1  from  BM_YBFPXM AA,VW_BM_YBSFDY CC,BM_YBSFXM DD
                 where  AA.BH0000 = CC.YBZYFP
                           and  AA.YBZXLB = CC.YBZXLB
                           and  CC.YBBRLB = LS_YBBRLB
                           and  CC.SFXMID = a.YPNM00
                           and  CC.YBZXLB = DD.YBZXLB(+)
                           and  CC.YBXMBH = DD.XMBH00(+)
                           and  CC.SFYP00 = 'Y'--是否药品,'0'费用,'1'零散处方,'2'医嘱处方
                           and  AA.YBZXLB = LS_YBZXLB
                           and  AA.SYBZ00 <> '1'
                           and  nvl(CC.SFYBXM,'N')='N'
                 )
                group by b.DJH000,b.BRID00
                 ;
     
       --冲销的单据号
     insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
     select LS_CXLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','N'
      from ZY_BRFY00 a where  1=1
      and a.ZYID00=PZYID00
      and a.BRID00=PBRID00
  	  and a.CXDJH0<>0
  	  and a.CXDJH0 in (select bb.DJH000 from  PAY_YJJ_MXCX bb where bb.BRID00=PBRID00 and bb.CXLSH0=LS_CXLSH0 and bb.DDLSH0=LS_DDLSH0 and bb.STATE='N' )
  	  and a.DJH000 not in (select bb.DJH000 from  PAY_YJJ_MXCX bb where bb.BRID00=PBRID00 and bb.CXLSH0=LS_CXLSH0 and bb.DDLSH0=LS_DDLSH0 and bb.STATE='N') 
      ;
                                     
     --查询数据自费金额
    begin
  	 select sum(AMOUNT) into PZFHJJE from  PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=LS_CXLSH0 and DDLSH0=LS_DDLSH0 and STATE='N';
  	exception
     when others then
       PZFHJJE:=0;
    end;

    if nvl(PZFHJJE,0)<=0 then
   	 PZFHJJE:=0;
   	 PFKLSH0:='';
   	 return;
    end if;

  	--把数据插入到中间表-->医保项目
     insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
      select LS_DDLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','Y'
      from ZY_BRFY00 a where  1=1
      and a.ZYID00=PZYID00 and a.BRID00=PBRID00
  	  and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
                              where  k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
                                     connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
      and
  	  (
  	  ( nvl(PZFHJJE,0)>0 and not exists (select 1 from  PAY_YJJ_MXCX aa where aa.DJH000=a.DJH000 and aa.BRID00=PBRID00
  	                                     and aa.CXLSH0=LS_CXLSH0 and aa.DDLSH0=LS_DDLSH0 and aa.STATE='N'))
  	  or (nvl(PZFHJJE,0)<=0 and 1=1)
  	  );


   --查询数据医保金额
    begin
  	 select sum(AMOUNT) into PYBHJJE from  PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=LS_CXLSH0 and DDLSH0=LS_DDLSH0 and STATE='Y';
  	exception
     when others then
       PYBHJJE:=0;
    end;


  end if;
  --1:出院结算 M:医保项目拆分---end---

 if PCOMMIT='Y' then
 	commit;
 end if;
 PZXZT00:='1';--执行状态 0:失败 1:成功
exception
  when no_data_found then
    PERRMSG:=substrb('执行过程SP_ZY_FYCFJS异常!错误原因:'||sqlerrm,1,200);
    PZXZT00:='0';--执行状态 0:失败 1:成功
    PERCODE:='no_data_found';
  when others   then
    PERRMSG:=substrb('执行过程SP_ZY_FYCFJS异常!错误原因:'||sqlerrm,1,200);
    PZXZT00:='0';--执行状态 0:失败 1:成功
    PERCODE:='others';
end;
/
show error;

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%